﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace GraphicSplit101
{
    public class DBUtils
    {
        private static readonly string connectionStr = System.Configuration.ConfigurationSettings.AppSettings["connectionString"];

        /// <summary>
        /// 根据SQL得到DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql)
        {
            SqlConnection conn = new SqlConnection(connectionStr);
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            conn.Open();
            da.Fill(dt);
            da.Dispose();
            conn.Dispose();
            return dt;
        }

        /// <summary>
        /// 执行SQL语句。该方法用事务执行
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteSql(string sql)
        {
            SqlConnection conn = new SqlConnection(connectionStr);
            conn.Open();
            SqlTransaction tran = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Transaction = tran;
            int count = 0;
            try
            {
                count = Convert.ToInt32(cmd.ExecuteNonQuery());
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
            }
            finally
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return count;
        }

        /// <summary>
        /// 根据SQL语句返回唯一值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object GetUniqueValue(string sql)
        {
            SqlConnection conn = new SqlConnection(connectionStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            object value = cmd.ExecuteScalar();
            cmd.Dispose();
            conn.Dispose();
            return value;
        }
    }
}